﻿Imports System.Data.SqlClient
Public Class dsHLV
    'Khoi tao chi co Ma va Ten
    Private _maHLV As Integer
    Private _tenHLV As String
    Private _ngaysinh As Date
    Private _quoctich As String
    Private _tenclb As String
    Sub New()
        _maHLV = 0
        _tenHLV = ""
    End Sub

    Public Property MaHLV() As Integer
        Get
            Return _maHLV
        End Get
        Set(value As Integer)
            _maHLV = value
        End Set
    End Property
    Public Property TenHLV As String
        Get
            Return _tenHLV
        End Get
        Set(value As String)
            _tenHLV = value
        End Set
    End Property

    Public Property NgaySinh As Date
        Get
            Return _ngaysinh
        End Get
        Set(value As Date)
            _ngaysinh = value
        End Set
    End Property
    Public Property QuocTich As String
        Get
            Return _quoctich
        End Get
        Set(value As String)
            _quoctich = value
        End Set
    End Property
    Public Property TenCLB As String
        Get
            Return _tenclb
        End Get
        Set(value As String)
            _tenclb = value
        End Set
    End Property

    Dim ctx As New QLDBDataContext()

    'Lay ma,ten, ngay sinh, quoc tich, ten HLV cho hlv
    Public Function LayHLV() As List(Of dsHLV)
        Dim q = From p In ctx.HUANLUYENVIENs
                Select New dsHLV With {.MaHLV = p.MaHLV, .TenHLV = p.TenHLV}
        Return q.ToList()
    End Function

    Private Function LayQT(ByVal MaQT) As String
        If MaQT Is Nothing Then
            Return "Chưa Cập Nhật"
        Else
            Dim ma As Integer = MaQT
            Dim v = From q In ctx.QUOCTICHes.Where(Function(p) p.MaQT = ma)
            If Not v Is Nothing Then
                Dim qt As List(Of QUOCTICH)
                qt = v.ToList()
                Return qt.Min.TenQT
            Else
                Return "Chưa Cập Nhật"
            End If
        End If
    End Function

    Private Function LayTenDoi(ByVal MaDoi) As String
        If MaDoi Is Nothing Then
            Return "Thất Nghiệp"
        End If
        Dim ma As Integer = MaDoi
        Dim v = (From q In ctx.CAULACBOs.Where(Function(p) p.MaCLB = ma)).Count
        If v <> 0 Then
            Dim hlv = From q In ctx.CAULACBOs.Where(Function(p) p.MaCLB = ma)
            Dim td As List(Of CAULACBO)
            td = hlv.ToList()
            Return td.Min.TenCLB
        Else
            Return "Thất Nghiệp"
        End If
    End Function
    Public Function LayDuLieuHLV() As IList

        Dim query = From p In ctx.HUANLUYENVIENs
                    Order By p.MaHLV Ascending
                    Select New dsHLV With {.TenHLV = p.TenHLV, .MaHLV = p.MaHLV, .NgaySinh = p.NgaySinh, .TenCLB = LayTenDoi(p.MaHLV), .QuocTich = LayQT(p.MaQT)}


        Return query.ToList()

    End Function

End Class
